package drive.Mapper;

import drive.Model.Article;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Mapper
public interface ArticleMapper {
    // 查询所有文章
    @Select("SELECT * FROM article")
    List<Article> findAll();

    // 根据ID查询文章
    @Select("SELECT * FROM article WHERE articleID = #{articleId}")
    Article findById(int articleId);

    //关键词查询
    @Select("SELECT * FROM article " +
            "WHERE title LIKE CONCAT('%', #{keyword}, '%') " +
            "OR content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR tag LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY recommendationIndex DESC")
    List<Article> selectArticlesByKeyword(String keyword);

    //更新收藏数量
    @Update("UPDATE article " +
            "SET favoriteCount = (SELECT COUNT(*) FROM favoriterecord WHERE contentID = #{articleID} AND contentType = 'article') " +
            "WHERE articleID = #{articleID}")
    void updateArticleFavoriteCountByArticleID(int articleID);

    //更新评论数量
    @Update("UPDATE article " +
            "SET commentCount = (SELECT COUNT(*) FROM Comment WHERE articleID = #{articleID}) " +
            "WHERE articleID = #{articleID}")
    void updateArticleCommentCountByArticleID(int articleID);

    // 新增文章
    @Insert("INSERT INTO article (authorID, title, content, likeCount, favoriteCount, commentCount, creationTime, recommendationIndex, status, longitude, latitude, tag) VALUES (#{authorID}, #{title}, #{content}, #{likeCount}, #{favoriteCount}, #{commentCount}, #{creationTime}, #{recommendationIndex}, #{status}, #{longitude}, #{latitude}, #{tag})")
    @Options(useGeneratedKeys = true, keyProperty = "articleID", keyColumn = "articleID")
    int add(Article article);

    // 更新文章
    @Update("UPDATE article SET authorID = #{authorID}, title = #{title}, content = #{content}, likeCount = #{likeCount}, favoriteCount = #{favoriteCount}, commentCount = #{commentCount}, status = #{status}, longitude = #{longitude}, latitude = #{latitude} WHERE articleID = #{articleID}")
    int update(Article article);

    @Update("UPDATE article SET title = #{title}, content = #{content} WHERE articleID = #{articleID}")
    int update_mini(Article article);

    @Update("UPDATE article SET recommendationIndex = recommendationIndex+1 WHERE articleID = #{articleID}")
    int update_re(Article article);
    // 删除文章
    @Delete("DELETE FROM article WHERE articleID = #{articleId}")
    int delete(int articleId);

    // 分页查询文章
    @Select("SELECT * FROM article Where authorID = #{userID} AND status IN ('published', 'pendingReview')  ORDER BY creationTime DESC LIMIT #{PageInit}, #{PageSize}")
    List<Article> findPaginatedByUserID(int userID,int PageInit, int PageSize);

    // 根据关键词分页查询文章
    @Select("SELECT * FROM article " +
            "WHERE (title LIKE CONCAT('%', #{keyword}, '%') " +
            "OR content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR tag LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND status IN ('published', 'pendingReview') " +
            "ORDER BY recommendationIndex DESC LIMIT #{offset}, #{pageSize}")
    List<Article> findPaginatedByKeyword(@Param("keyword") String keyword, @Param("offset") int offset, @Param("pageSize") int pageSize);

    // 获取文章总数
    @Select("SELECT COUNT(*) FROM article")
    long countAll();

    // 根据关键词获取文章总数
    @Select("SELECT COUNT(*) FROM article " +
            "WHERE title LIKE CONCAT('%', #{keyword}, '%') " +
            "OR content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR tag LIKE CONCAT('%', #{keyword}, '%')" +
            "AND status = 'published')")
    long countByKeyword(@Param("keyword") String keyword);

    @Select("SELECT COUNT(*) FROM article WHERE authorID = #{userId} AND status = 'published'")
    int countPublishedArticlesByUser(int userId);

    /**
     * 获取未审核的文章
     * @return 文章列表
     */
    @Select("SELECT * FROM article WHERE status = 'pendingReview'")
    List<Article> getPendingReviewArticles();

    /**
     * 统计文章总量
     * @return 文章总数
     */
    @Select("SELECT COUNT(*) FROM article")
    long countArticles();

    /**
     * 按月获取文章总量
     * @return 每月文章总量列表
     */
    @Select("SELECT DATE_FORMAT(creationTime, '%Y-%m') AS month, COUNT(*) AS count FROM article GROUP BY DATE_FORMAT(creationTime, '%Y-%m') ORDER BY month")
    List<Map<String, Object>> getArticleCountMonthly();

    /**
     * 根据作者ID列表获取文章列表（分页）
     * @param authorIds 作者ID列表
     * @param offset 偏移量
     * @param pageSize 每页大小
     * @return 文章列表
     */
    @Select("<script>" +
            "SELECT * FROM article " +
            "WHERE authorID IN " +
            "<foreach collection='authorIds' item='id' open='(' separator=',' close=')'>#{id}</foreach> " +
            "ORDER BY creationTime DESC " +
            "LIMIT #{offset}, #{pageSize}" +
            "</script>")
    List<Article> getArticlesByAuthorIds(List<Integer> authorIds, int offset, int pageSize);
    /**
     * 根据作者ID列表获取文章总数
     * @param authorIds 作者ID列表
     * @return 文章总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM article " +
            "WHERE authorID IN " +
            "<foreach collection='authorIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +
            "</script>")
    int getArticlesCountByAuthorIds(List<Integer> authorIds);

    /**
     * 根据经纬度上下限获取文章
     * @param minLatitude 最小纬度
     * @param maxLatitude 最大纬度
     * @param minLongitude 最小经度
     * @param maxLongitude 最大经度
     * @return 文章列表
     */
    @Select("SELECT * FROM article WHERE (latitude BETWEEN #{minLatitude} AND #{maxLatitude} AND longitude BETWEEN #{minLongitude} AND #{maxLongitude}) AND status IN ('published', 'pendingReview') ORDER BY creationTime DESC LIMIT 20")
    List<Article> getArticlesByLocation(double minLatitude, double maxLatitude, double minLongitude, double maxLongitude);
}